package com.ruoyi.web.service;


import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



@Service
public class C2CodeSubmissionActivityRankingService {

    @Autowired
    private DruidDataSource druidDataSource;

    /**
     * 获取过去30天 `moduleName` 出现次数前10名及其次数
     *
     * @return 排序后的前10名数据
     */
    public List<Map<String, Object>> getTop10FullNameCounts() {
        List<Map<String, Object>> result = new ArrayList<>();

        // 获取最近30天的开始和结束时间
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime thirtyDaysAgo = now.minusDays(30);

        String startTime = thirtyDaysAgo.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        String endTime = now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        // SQL 查询语句
        String sql = "SELECT moduleName, COUNT(*) AS count " +
                "FROM code_merge_info " +
                "WHERE mergeTime >= ? AND mergeTime < ? " +
                "GROUP BY moduleName " +
                "ORDER BY count DESC " +
                "LIMIT 10";

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            // 设置查询参数
            stmt.setString(1, startTime);
            stmt.setString(2, endTime);

            // 执行查询并处理结果
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    Map<String, Object> data = new HashMap<>();
                    data.put("moduleName", rs.getString("moduleName"));
                    data.put("count", rs.getInt("count"));
                    result.add(data);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }


    /**
     * 获取过去30天 `moduleName` 出现次数前10名及其次数 release次数
     *
     * @return 排序后的前10名数据
     */
    public List<Map<String, Object>> getTop10WithReleaseCounts() {
        List<Map<String, Object>> result = new ArrayList<>();

        // 调用现有方法获取 Top10 数据
        List<Map<String, Object>> top10FullNameCounts = getTop10FullNameCounts();

        // 获取最近30天的开始和结束时间
        LocalDateTime now = LocalDateTime.now();
        LocalDateTime thirtyDaysAgo = now.minusDays(30);

        String startTime = thirtyDaysAgo.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
        String endTime = now.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));

        // 遍历 Top10 数据并查询对应的 module_release_his_info 数据
        String sql = "SELECT COUNT(*) AS releaseCount " +
                "FROM module_release_his_info " +
                "WHERE moduleName = ? AND fullName = ? " +
                "AND modifyTime >= ? AND modifyTime < ?";

        try (Connection conn = druidDataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            for (Map<String, Object> item : top10FullNameCounts) {
                String moduleNameFull = (String) item.get("moduleName");
                int mergeCount = (int) item.get("count");

                // 分割 moduleName 为两部分
                String[] parts = moduleNameFull.split("-");
                if (parts.length == 2) {
                    String moduleName = parts[0];
                    String fullName = parts[1];

                    // 设置查询参数
                    stmt.setString(1, moduleName);
                    stmt.setString(2, fullName);
                    stmt.setString(3, startTime);
                    stmt.setString(4, endTime);

                    // 查询 releaseCount 并添加到结果中
                    try (ResultSet rs = stmt.executeQuery()) {
                        if (rs.next()) {
                            int releaseCount = rs.getInt("releaseCount");

                            // 构建最终结果
                            Map<String, Object> data = new HashMap<>();
                            data.put("moduleName", moduleNameFull);
                            data.put("mergeCount", mergeCount);
                            data.put("releaseCount", releaseCount);

                            result.add(data);
                        }
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}



//@Service
//public class C2CodeSubmissionActivityRankingService {
//
//    @Autowired
//    private DruidDataSource druidDataSource;
//
//    /**
//     * 获取当前自然月 `fullName` 出现次数前10名及其次数
//     *
//     * @return 排序后的前10名数据
//     */
//    public List<Map<String, Object>> getTop10FullNameCounts() {
//        List<Map<String, Object>> result = new ArrayList<>();
//
//        // 获取当前月的开始和结束时间
//        LocalDate firstDayOfMonth = LocalDate.now().withDayOfMonth(1);
//        LocalDate firstDayOfNextMonth = firstDayOfMonth.plusMonths(1);
//
//        String startOfMonth = firstDayOfMonth.atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
//        String endOfMonth = firstDayOfNextMonth.atStartOfDay().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
//
//        // SQL 查询语句
//        String sql = "SELECT fullName, COUNT(*) AS count " +
//                "FROM code_merge_info " +
//                "WHERE mergeTime >= ? AND mergeTime < ? " +
//                "GROUP BY fullName " +
//                "ORDER BY count DESC " +
//                "LIMIT 10";
//
//        try (Connection conn = druidDataSource.getConnection();
//             PreparedStatement stmt = conn.prepareStatement(sql)) {
//
//            // 设置查询参数
//            stmt.setString(1, startOfMonth);
//            stmt.setString(2, endOfMonth);
//
//            // 执行查询并处理结果
//            try (ResultSet rs = stmt.executeQuery()) {
//                while (rs.next()) {
//                    Map<String, Object> data = new HashMap<>();
//                    data.put("fullName", rs.getString("fullName"));
//                    data.put("count", rs.getInt("count"));
//                    result.add(data);
//                }
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }
//
//        return result;
//    }
//}
